package com.millstein.tsinglog.mapper.biz;

import com.millstein.tsinglog.mapper.BaseMapper;
import com.millstein.tsinglog.model.pojo.Article;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

public interface ArticleMapper extends BaseMapper<Article> {

    /**
     * server用，根据id查询文章
     * @param id
     * @return
     */
    @Select("SELECT `id`, `title`, `category_id` categoryId, `type`, `is_release` isRelease, " +
            "`can_comment` canComment, `up_to_top` upToTop, `release_time` releaseTime, `status`, " +
            "`create_time` createTime, `update_time` updateTime FROM `biz_article` " +
            "where `status`= 1 and `id` = #{id};")
    Article selectArticleByPrimaryKeyForServer(@Param("id") Long id);

    /**
     * web用，根据id查询文章
     * @param id
     * @return
     */
    @Select("SELECT `id`, `title`, `category_id` categoryId, `type`, `is_release` isRelease, " +
            "`can_comment` canComment, `up_to_top` upToTop, `release_time` releaseTime, `status`, " +
            "`create_time` createTime, `update_time` updateTime FROM `biz_article` " +
            "where `status`= 1 and `is_release` = 1 and `id` = #{id};")
    Article selectArticleByPrimaryKeyForWeb(@Param("id") Long id);

    /**
     * 向中间表biz_article_tag中插入数据
     * @param id 文章id
     * @param tagId 标签id
     * @param date 插入时间
     * @return
     */
    @Insert("insert into biz_article_tag (id, aid, tid, status, create_time, update_time) " +
            "values (null, #{aid}, #{tid}, 1, #{date}, #{date});")
    int insertArticleAndTag(@Param("aid") Long id, @Param("tid") Long tagId, @Param("date") String date);

    /**
     * 根据文章id查询该文章标签的集合
     * @param id 文章id
     * @return
     */
    @Select("select tid from biz_article_tag where aid = #{id}")
    List<Long> getTagsByArticleId(@Param("id") Long id);

    /**
     * 根据文章id删除中间表biz_article_tag中对应文章的数据
     * @param id 文章id
     */
    @Delete("delete from biz_article_tag where aid = #{id}")
    int deleteByAid(@Param("id") Long id);

    /**
     * 获取最近有回复的文章的集合
     * @return
     */
    @Select("SELECT a.id, a.title, a.category_id categoryId, a.type, a.is_release isRelease, a.can_comment canComment" +
            ", a.up_to_top upToTop, a.release_time releaseTime, a.status, a.create_time createTime, a.update_time " +
            "updateTime " +
            "FROM biz_article a " +
            "LEFT OUTER JOIN( " +
            "SELECT aid, check_time FROM biz_comment WHERE comment_status = 2 and `status` = 1 GROUP BY aid) tmp " +
            "ON tmp.aid = a.id " +
            "WHERE a.is_release = 1 and a.status = 1 " +
            "order by tmp.check_time desc, a.release_time desc")
    List<Article> getArticleForLatestReply();

    /**
     * 获取最近发表的文章的集合
     * @return
     */
    @Select("SELECT id, title, category_id categoryId, type, is_release isRelease, can_comment canComment, up_to_top " +
            "upToTop, release_time releaseTime, status, create_time createTime, update_time updateTime " +
            "from `biz_article` where is_release = 1 and status = 1 order by up_to_top desc, release_time " +
            "desc")
    List<Article> getArticleForLatestRelease();

    /**
     * 获取文章数据，按照评论数量的降序排列
     * @return
     */
    @Select("SELECT a.id, a.title FROM biz_article a " +
            "LEFT OUTER JOIN(" +
            "SELECT aid, COUNT(*) as count FROM biz_comment WHERE comment_status = 2 and `status` = 1 GROUP BY aid) " +
            "tmp ON tmp.aid = a.id " +
            "WHERE a.is_release = 1 and a.status = 1 " +
            "order by tmp.count desc, release_time desc")
    List<Map<String, Object>> getHotArticle();

    /**
     * 根据标签获取文章列表
     * @param tid 标签id
     * @return
     */
    @Select("select a.id, a.title, a.type, a.release_time,  ac.cover_image, ac.editor_type, ac.article_summary " +
            "from `biz_article` a " +
            "left join `biz_article_tag` at on a.id = at.aid " +
            "left join `biz_tag` t on at.tid = t.id " +
            "left join `biz_article_content` ac on ac.article_id = a.id " +
            "where a.is_release = 1 and a.status = 1 and t.id = #{tid} " +
            "group by a.id " +
            "order by release_time desc")
    List<Map<String, Object>> getArticleByTag(@Param("tid") Long tid);

    /**
     * 在文章浏览表中插入记录
     * @param id 文章id
     * @param ip 用户ip地址
     * @param currentDateStr 操作时间
     * @return
     */
    @Insert("insert into `biz_article_browse` values (null, #{id}, #{ip}, #{date}, #{date}, #{date})")
    int insertArticleBrowse(@Param("id") Long id, @Param("ip") String ip, @Param("date") String currentDateStr);

    /**
     * 在文章喜欢表中插入记录
     * @param id 文章id
     * @param ip 用户ip地址
     * @param currentDateStr 操作时间
     * @return
     */
    @Insert("insert into `biz_article_prefer` values (null, #{id}, #{ip}, #{date}, #{date}, #{date})")
    int insertArticlePrefer(@Param("id") Long id, @Param("ip") String ip, @Param("date") String currentDateStr);

    /**
     * 获取指定文章的浏览数
     * @param id 文章id
     * @return
     */
    @Select("select count(1) from `biz_article_browse` where `aid` = #{aid}")
    int getBrowseCountForArticle(@Param("aid") Long id);

    /**
     * 获取指定文章的评论数
     * @param id 文章id
     * @return
     */
    @Select("select count(1) from `biz_comment` where `aid` = #{aid} and `comment_status` = 2 and `status` = 1")
    int getCommentCountForArticle(@Param("aid") Long id);

    /**
     * 获取指定文章的喜欢数
     * @param id 文章id
     * @return
     */
    @Select("select count(1) from `biz_article_prefer` where `aid` = #{aid}")
    int getPreferCountForArticle(@Param("aid") Long id);

    /**
     * 删除文章的浏览数据
     * @param id 文章id
     */
    @Delete("delete from `biz_article_browse` where aid = #{aid}")
    void deleteArticleBrowseById(@Param("aid") Long id);

    /**
     * 删除文章的喜欢数据
     * @param id 文章id
     */
    @Delete("delete from `biz_article_prefer` where aid = #{aid}")
    void deleteArticlePreferById(@Param("aid") Long id);
}
